/*
 * Copyright 2016-2019 yoara
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package yhao.infra.web.common.excel;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

/**
 * excel导出下载工具
 *
 * @author yoara
 */
public class ExportExcelUtil{
    /**
     * 通过id获得sqlMap
     *
     * @param key
     * @return
     */
    public static String pickSqlMap(String key) {
        try {
            Map<String, Object> excelMap = ExcelUtils.getConfigById(key);
            if (excelMap != null) {
                return (String) excelMap.get(ExcelUtils.CONFIG_EXCEL_SQL_MAPS_ID);
            }
        } catch (Exception e) {}
        return null;
    }

    /**
     * 导出excel文件
     *
     * @param key        关联excel的ID
     * @param exportData 关联excel的导出数据
     * @param response
     */
    public static void excelExport(String key, List exportData, HttpServletResponse response) throws Exception {
        Map<String, Object> excelMap = ExcelUtils.getConfigById(key);
        String fileName = "";
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("sheet");
        if (excelMap != null) {
            List<Map<String, Object>> columns = (List<Map<String, Object>>) excelMap.get(ExcelUtils.CONFIG_EXCEL_COLUMNS);
            fileName = (String) excelMap.get(ExcelUtils.CONFIG_EXCEL_FILE_NAME);
            JSONArray array = beanToJSON(exportData);
            createHeader(sheet, columns);
            createBook(workbook, sheet, array, columns);
        }
        exportExcel(workbook, response, fileName);
    }

    /**
     * 转换成JSONARRAY
     */
    private static JSONArray beanToJSON(List<?> list) {
        JSONArray array = new JSONArray();
        for (Object obj : list) {
            array.add(JSONObject.parseObject(JSON.toJSONString(obj)));
        }
        return array;
    }

    /**
     * 构建表头
     */
    private static void createHeader(HSSFSheet sheet, List<Map<String, Object>> columns) {
        HSSFRow row = sheet.createRow(0);
        for (int index = 0; index < columns.size(); index++) {
            Map<String, Object> column = columns.get(index);
            HSSFCell cell = row.createCell(index);
            cell.setCellValue((String) column.get(ExcelUtils.CONFIG_COLUMN_HEAD));
            sheet.setColumnWidth(index, column.get("width") == null
                    ? 200 : new BigDecimal((String) column.get("width")).intValue() * 36);
        }
    }

    /**
     * 构建数据表格
     */
    private static void createBook(HSSFWorkbook workbook, HSSFSheet sheet,
                            JSONArray array, List<Map<String, Object>> columns) {
        if (array == null) {
            return;
        }
        //数据格式化
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));

        for (int i = 0; i < array.size(); i++) {
            JSONObject item = array.getJSONObject(i);
            HSSFRow row = sheet.createRow(i + 1);
            for (int index = 0; index < columns.size(); index++) {
                Map<String, Object> column = columns.get(index);
                HSSFCell cell = row.createCell(index);
                try {
                    String value = item.getString((String) column.get(ExcelUtils.CONFIG_COLUMN_KEY));
                    if (value == null || value.equals("") || value.equals("{}")) {
                        value = "";
                    }
                    String dataType = (String) column.get(ExcelUtils.CONFIG_COLUMN_DATA_TYPE);
                    if (StringUtils.isNotEmpty(dataType)) {
                        if (ExcelUtils.CONFIG_COLUMN_DATA_TYPE_NUMBER.equals(dataType)) {
                            cell.setCellStyle(cellStyle);
                            cell.setCellValue(Double.valueOf(value));
                        } else {
                            cell.setCellValue(value);
                        }
                    } else {
                        cell.setCellValue(value);
                    }
                } catch (Exception e) {
                    cell.setCellValue("");
                }
            }
        }
    }

    /**
     * 导出excel
     */
    private static void exportExcel(HSSFWorkbook workbook, HttpServletResponse response, String fileName) throws Exception {
        String exportFileName = StringUtils.isEmpty(fileName) ? "电子表格.xls" : fileName + ".xls";
        response.setHeader("Content-disposition", "attachment;filename = " + URLEncoder.encode(exportFileName, "UTF-8"));
        OutputStream stream = response.getOutputStream();
        workbook.write(stream);
        stream.close();
    }
}
